-- =============================================
-- Function: CalInterestWkly
-- Purpose:	
-- Author: Garret Stephenson
-- Created: 2013-5-10
-- Modification History
-- Modified By	Modification Date	Reason
-- =====================================================================================
--  Dale McFarlane    2014-1-29    Factor in early closed data of loans
-- =====================================================================================
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'CalInterestWkly') AND xtype IN (N'FN', N'IF', N'TF'))
    DROP FUNCTION CalInterestWkly
GO


CREATE FUNCTION [dbo].[CalInterestWkly]
(
	@report_month INTEGER,
	@report_year INTEGER,
	@principal DECIMAL(18, 2),
	@interest_rate DECIMAL(18, 8),
	@loan_start_date DATETIME,	
	@loan_close_date DATETIME,	
	@term INTEGER,	
	@payment_amount DECIMAL(18, 2)	
)
RETURNS DECIMAL(18, 2)
AS
BEGIN

	DECLARE @interest DECIMAL(18, 8),
		@payment_date DATETIME, --set first payment DATETIME 1 week afte disbursement DATETIME
		@counter INTEGER,
		@report_date DATETIME 
		
	/*check for valid values and return -1 if invalid*/	
	IF @report_month <=0 OR 
		@report_month > 12 OR 
		@report_year <= 1990 OR 
		@report_year > 2030 OR
		@interest_rate <= 0 OR 
		@interest_rate > 100 OR
		@term <= 0 OR 
		@principal <= 0 OR 
		@payment_amount <= 0
	BEGIN
		RETURN -1
	END
	
	SET @interest = 0
	SET @counter = 1	
	SEt @payment_date = DATEADD(wk, 1, @loan_start_date)
	SET @report_date = dbo.DateFromParts --end of the the month for which the report is required
	(
		@report_year, 
		@report_month, 
		DAY(DATEADD(mm, DATEDIFF(mm, -1, dbo.DateFromParts(@report_year, @report_month, 1)), -1))
	)	

	SET @interest_rate = (@interest_rate / 100) / 52 --weekly interest rate

	/*ensure that atleast one payment exist before the report DATETIME*/
	IF (@loan_start_date < @report_date AND @loan_close_date IS NULL) OR (@loan_start_date < @report_date AND @loan_close_date IS NOT NULL AND @payment_date < @loan_close_date)
	BEGIN
		/*loops through reducing the balance each week from the start of the loan until the month before the report*/
		WHILE (YEAR(@payment_date) < @report_year OR (YEAR(@payment_date) = @report_year AND MONTH(@payment_date) < @report_month))
		BEGIN	
			IF @counter <= @term --ensure that interest is calculated not exeeding the number of terms
			BEGIN
				SET @interest = @principal * @interest_rate  --calculate weekly interest
				SET @principal = @principal + @interest - @payment_amount --reduce the balance
				SET @payment_date = DATEADD(wk, 1, @payment_date)--set next payment DATETIME- 1 week ahead
				SET @counter = @counter + 1
			END	
			ELSE
			BEGIN
				BREAK
			END	
		END	
		
		SET @interest = 0 --reset interest- disregarding interest for months before the report
		
		/*Compute interest for the month of the report. The month of the payment DATETIME is the same as the month of the report*/
		WHILE ((@payment_date <= @report_date AND @loan_close_date IS NULL) OR (@loan_close_date IS NOT NULL AND @payment_date < @loan_close_date))
		BEGIN
			IF @counter <= @term
			BEGIN
				SET @interest = @interest + (@principal * @interest_rate)  --calculate weekly interest
				SET @principal = @principal + (@principal * @interest_rate) - @payment_amount -- reduce the balance
				SET @payment_date = DATEADD(wk, 1, @payment_date) --set next payment DATETIME - 1 week ahead and to break loop
				SET @counter = @counter + 1
			END	
			ELSE
			BEGIN
				BREAK
			END	
		END
	END
	RETURN @interest --return the interest for the month
END;

GO


